﻿using System;
using System.Text;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
//using System.Web;
//using System.Web.Security;
//using System.Web.UI;
//using System.Web.UI.HtmlControls;
//using System.Web.UI.WebControls;
//using System.Web.UI.WebControls.WebParts;
using System.IO;
//using Wiscom.Data;
//using Excel = Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace hxlxc
{
    class excle_exp_or_imp
    {
        /**/
        /// <summary>
        ///  <br>获取Excel中的表名字</br>
        /// <br>2008-07-22 ljx</br>
        /// </summary>
        /// <param name="filepath">Excel文件路径</param>

        public static string GetExcelSheetName(string filepath)
        {
            string filetablename = "";
            //ArrayList al = new ArrayList();
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                conn.Open();
                System.Data.DataTable sheetNames = conn.GetOleDbSchemaTable
                (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                conn.Close();

                // filetablename = sheetNames.Rows.Rows[2].ToString();
                foreach (DataRow dr in sheetNames.Rows)
                {
                    //al.Add(dr[2]);
                    filetablename = dr[2].ToString();
                    break;

                }
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("====");
                System.Console.WriteLine(ex.ToString());
                System.Console.WriteLine("====");
            }


            return filetablename;
        }

        /**/
        /// <summary>
        ///  <br>excel文件读取到DataSet</br>
        /// <br>2008-07-22 ljx</br>
        /// </summary>      
        /// <param name="filepath">Excel文件路径</param>
        /// <param name="sheetname">Excel文件中的表</param>
        public static System.Data.DataTable ExcelDataSet(string filepath, string sheetname)
        {
            string strConn;
            System.Data.DataTable dataTable = null;
            DataSet ds = new DataSet();
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
                oada.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            try
            {
                dataTable = ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dataTable;

        }

        //Excel.Application objApp = new Excel.ApplicationClass();
        //public static DataTable excelredcell()
        //{
        //    Excel.Sheets objSheets;
        //    Excel._Worksheet objSheet;
        //    Excel.Range range;
        //    try
        //    {
        //        try
        //        {
        //            objApp.Workbooks.Open(@"C:\人民币.xls ", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        //            objSheets = objApp.Worksheets;//引用工作簿中的工作表 
        //            objSheet = (Excel._Worksheet)objSheets.get_Item(1);//得到工作表组中第一个工作表 

        //        }

        //        catch (Exception theException)
        //        {
        //            String errorMessage;
        //            errorMessage = "Can't find the Excel workbook.  Try clicking Button1 " +
        //               "to create an Excel workbook with data before running Button2.";
        //            MessageBox.Show(errorMessage, "Missing Workbook?");
        //            return;
        //        }

        //        range = objSheet.get_Range("A12", "D12");

        //        Object[,] saRet;
        //        saRet = (System.Object[,])range.get_Value(Missing.Value);

        //        //Determine the dimensions of the array.
        //        long iRows;
        //        long iCols;
        //        iRows = saRet.GetUpperBound(0);
        //        iCols = saRet.GetUpperBound(1);

        //        //Build a string that contains the data of the array.
        //        String valueString = "";


        //        for (long rowCounter = 1; rowCounter <= iRows; rowCounter++)
        //        {
        //            for (long colCounter = 1; colCounter <= iCols; colCounter++)
        //            {

        //                //Write the next value into the string.
        //                if (saRet[rowCounter, colCounter] == null)
        //                {
        //                    valueString = "0";

        //                }
        //                else
        //                {
        //                    valueString = saRet[rowCounter, colCounter].ToString();
        //                }
        //                MessageBox.Show(valueString, "Array Values");
        //            }
        //        }
        //    }

        //    catch (Exception theException)
        //    {
        //        String errorMessage;
        //        errorMessage = "Error: ";
        //        errorMessage = String.Concat(errorMessage, theException.Message);
        //        errorMessage = String.Concat(errorMessage, " Line: ");
        //        errorMessage = String.Concat(errorMessage, theException.Source);

        //        MessageBox.Show(errorMessage, "Error");
        //    }
        //    finally
        //    {
        //        objApp.Workbooks.Close();

        //    }

        //}


        ///**/
        ///// <summary>
        ///// 导出 DataSet为Excel文件格式[!导出的Excel没有加工!]
        ///// <br>2008-07-22 ljx</br>
        ///// </summary>
        ///// <param name="strFileName">导出名称</param>
        ///// <param name="dt">DataSet</param>
        //public static void ExportExcel(string strFileName, DataSet dt)
        //{
        //    //清除Response缓存内容
        //    HttpContext.Current.Response.Clear();
        //    HttpContext.Current.Response.Buffer = true;
        //    strFileName = strFileName + ".xls";
        //    //确定字符的编码格式
        //    HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName));
        //    HttpContext.Current.Response.ContentType = "aapplication/vnd.ms-excel";
        //    HttpContext.Current.Response.Charset = "gb2312";
        //    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
        //    GridView dv = new GridView();
        //    dv.DataSource = dt;
        //    //dv.RowDataBound += GridView2_RowDataBound;//事件
        //    dv.DataBind();


        //    try
        //    {
        //        dv.Page.EnableViewState = false;
        //    }
        //    catch
        //    { }
        //    System.IO.StringWriter swBody = new System.IO.StringWriter();
        //    System.Web.UI.HtmlTextWriter hwBody = new System.Web.UI.HtmlTextWriter(swBody);
        //    dv.RenderControl(hwBody);
        //    //消除乱码特别设定，非常规方法
        //    string strExcel = "";
        //    strExcel = "";
        //    strExcel += hwBody.InnerWriter.ToString();
        //    HttpContext.Current.Response.Write(strExcel);
        //    HttpContext.Current.Response.End();
        //}


        ///**/
        ///// <summary>
        ///// 已控件格式导出(2008-07-23)
        ///// public override void VerifyRenderingInServerForm(Control control)  
        ///// 必须调用该函数之前,重写VerifyRenderingInServerForm
        ///// </summary>
        ///// <param name="ctl">GridView控件</param>
        ///// <param name="FileName">文件输出名字</param>

        //public static void ToExcel(Control ctl, string FileName)
        //{
        //    HttpContext.Current.Response.Charset = "Gb2312";
        //    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
        //    HttpContext.Current.Response.ContentType = "application/ms-excel";
        //    HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + FileName);
        //    ctl.Page.EnableViewState = false;
        //    System.IO.StringWriter tw = new System.IO.StringWriter();
        //    HtmlTextWriter hw = new HtmlTextWriter(tw);
        //    ctl.RenderControl(hw);
        //    HttpContext.Current.Response.Write(tw.ToString());
        //    HttpContext.Current.Response.End();
        //}

        //public static void ToExcel(String Sql, string FileName)
        //{
        //    //Add Response header
        //    HttpContext.Current.Response.Clear();
        //    HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.csv", FileName));

        //    HttpContext.Current.Response.ContentType = "application/ms-excel";
        //    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
        //    //GET Data From Database
        //    try
        //    {

        //        SqlDataReader dr = (SqlDataReader)DataHelper.GetDataReader(Sql);
        //        StringBuilder sb = new StringBuilder();
        //        //
        //        //Add Header
        //        //
        //        for (int count = 0; count < dr.FieldCount; count++)
        //        {
        //            if (dr.GetName(count) != null)
        //                sb.Append(dr.GetName(count));
        //            if (count < dr.FieldCount - 1)
        //            {
        //                sb.Append(",");
        //            }
        //        }
        //        HttpContext.Current.Response.Write(sb.ToString() + "\n");
        //        HttpContext.Current.Response.Flush();
        //        //
        //        //Append Data
        //        //
        //        while (dr.Read())
        //        {
        //            sb = new StringBuilder();

        //            for (int col = 0; col < dr.FieldCount - 1; col++)
        //            {
        //                if (!dr.IsDBNull(col))
        //                    sb.Append(dr.GetValue(col).ToString().Replace(",", " "));
        //                sb.Append(",");
        //            }
        //            if (!dr.IsDBNull(dr.FieldCount - 1))
        //                sb.Append(dr.GetValue(dr.FieldCount - 1).ToString().Replace(",", " "));
        //            HttpContext.Current.Response.Write(sb.ToString() + "\n");
        //            HttpContext.Current.Response.Flush();
        //        }
        //        dr.Dispose();
        //    }
        //    catch (Exception ex)
        //    {
        //        HttpContext.Current.Response.Write(ex.Message);
        //    }
        //    finally
        //    {

        //    }
        //    HttpContext.Current.Response.End();
        //}


        //public static void ToExcel(DataSet ds, string strExcelFileName)
        //{

        //    Excel.Application excel = new Excel.Application();

        //    //            Excel.Workbook obj=new Excel.WorkbookClass();
        //    //            obj.SaveAs("c:\zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);

        //    int rowIndex = 1;
        //    int colIndex = 0;

        //    excel.Application.Workbooks.Add(true);


        //    System.Data.DataTable table = ds.Tables[0];
        //    foreach (DataColumn col in table.Columns)
        //    {
        //        colIndex++;
        //        excel.Cells[1, colIndex] = col.ColumnName;
        //    }

        //    foreach (DataRow row in table.Rows)
        //    {
        //        rowIndex++;
        //        colIndex = 0;
        //        foreach (DataColumn col in table.Columns)
        //        {
        //            colIndex++;
        //            excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
        //        }
        //    }
        //    excel.Visible = false;

        //    //excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS", Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null);
        //    excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS", Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

        //    //wkbNew.SaveAs strBookName


        //    //excel.Save(strExcelFileName);
        //    excel.Quit();
        //    excel = null;

        //    GC.Collect();//垃圾回收
        //}






        /**/
        /// <summary>
        /// 删除Excel文件
        /// 2008-07-02 ljx
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        public static void DelExcel(string FilePath)
        {

            FileInfo ExcelFile = new FileInfo(FilePath);
            ExcelFile.Delete();

        }

        #region

        ///**/
        ///// <summary>
        ///// 得到宿舍信息Excel文件的DataSet
        ///// <br>2008-07-22 ljx</br>
        ///// </summary>
        ///// <param name="filepath">Excel文件路径</param>
        ///// <param name="sheetname">Excel文件中的表</param>
        ///// <returns></returns>
        //public static DataSet Get_Ssxx_DataSet(string filepath, string sheetname) //把excel文件读取到DataSet
        //{
        //    string strConn;
        //    DataSet ds = new DataSet();
        //    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        //    OleDbConnection conn = new OleDbConnection(strConn);
        //    try
        //    {
        //        OleDbDataAdapter oada = new OleDbDataAdapter("select 学号,姓名,学院,楼栋,寝室号 from [" + sheetname + "]", strConn);
        //        oada.Fill(ds);

        //    }
        //    catch (Exception ex) { }
        //    return ds;

        //}


        //public static DataSet Get_StudentInfo_DataSet(string filepath, string sheetname) //把excel文件读取到DataSet
        //{
        //    string strConn;
        //    DataSet ds = new DataSet();
        //    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        //    OleDbConnection conn = new OleDbConnection(strConn);
        //    try
        //    {
        //        OleDbDataAdapter oada = new OleDbDataAdapter("select 学号,姓名,性别,院系代码,专业代码,班级代码,学籍状态,出生年月,生源地,籍贯,身份证号,民族编码,政治面貌码 from [" + sheetname + "]", strConn);
        //        oada.Fill(ds);

        //    }
        //    catch (Exception ex) { }
        //    return ds;

        //}





        ///**/
        ///// <summary>
        ///// 得到宿舍信息Excel文件的DataSet
        ///// <br>2008-07-22 ljx</br>
        ///// </summary>
        ///// <param name="filepath">文件路径</param>
        ///// <param name="sheetname">Excel文件中的表</param>
        ///// <returns></returns>
        //public static DataSet Get_Ssxx_DataSet(string filepath, ArrayList sheetname) //把excel文件读取到DataSet
        //{
        //    string strConn;
        //    DataSet ds = new DataSet();
        //    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        //    OleDbConnection conn = new OleDbConnection(strConn);
        //    try
        //    {
        //        OleDbDataAdapter oada = new OleDbDataAdapter("select 学号,姓名,学院,楼栋,寝室号 from [" + sheetname + "]", strConn);
        //        oada.Fill(ds);

        //    }
        //    catch (Exception ex) { }
        //    return ds;

        //}

        #endregion

    }
}
